using System;
using System.Collections.Generic;
using System.Data;
using System.Text;

using Kenly.DBFramework;

namespace SchemaProvider
{
    public abstract class SchemaLoaderBase : ISchemaLoader
    {
        private string _connectionString;

        public string ConnectionString
        {
            get
            {
                return _connectionString;
            }
            set
            {
                _connectionString = value;
                this.Database.ConnectionString = this.ConnectionString;
            }
        }

        protected abstract IDatabase Database
        {
            get;
            set;
        }

        /// <summary>
        /// Gets a set of identity names,the table name is used as key and identity name is used as value.
        /// </summary>
        /// <returns></returns>
        public virtual Dictionary<string, string> ListIdentities()
        {
            return GetIdentityDictionary();
        }


        /// <summary>
        /// Gets a set of identity names,the table name is used as key and identity name is used as value.
        /// </summary>
        /// <returns></returns>
        private Dictionary<string, string> GetIdentityDictionary()
        {
            Dictionary<string, string> identityDictionary = new Dictionary<string, string>();
            try
            {
                DataTable identityTable = this.Database.CreateViewCommandExecutor().ExecuteDataTable(
                    this.ListIdentitiesSql(), false);

                if (identityTable == null || identityTable.Columns.Count <= 0)
                {
                    return identityDictionary;
                }

                foreach (DataRow row in identityTable.Rows)
                {
                    if (!(row["IdentityName"] is DBNull))
                    {
                        identityDictionary.Add(row["TableName"].ToString(), row["IdentityName"].ToString());
                    }
                }
            }
            catch
            {
            }
            return identityDictionary;
        }

        /// <summary>
        /// Gets a set of  PrimaryKeys,the table name is used as key and primary key is used as value.
        /// </summary>
        /// <returns></returns>
        public virtual Dictionary<string, string> ListPrimaryKeys()
        {
            return GetPrimaryKeyDictionary();
        }


        /// <summary>
        /// Gets a set of  PrimaryKeys,the table name is used as key and primary key is used as value.
        /// </summary>
        /// <returns></returns>
        private Dictionary<string, string> GetPrimaryKeyDictionary()
        {
            Dictionary<string, string> primaryKeyDictionary = new Dictionary<string, string>();

            try
            {
                //DataTable primaryKeyTable = GetPrimaryKeyTable();
                DataTable primaryKeyTable = this.Database.CreateViewCommandExecutor().ExecuteDataTable(
                    this.ListPrimaryKeysSql(), false);

                if (primaryKeyTable == null || primaryKeyTable.Columns.Count <= 0)
                {
                    return primaryKeyDictionary;
                }

                foreach (DataRow row in primaryKeyTable.Rows)
                {
                    if (!(row["PrimaryKey"] is DBNull))
                    {
                        primaryKeyDictionary.Add(row["TableName"].ToString(), row["PrimaryKey"].ToString());
                    }
                }
            }
            catch
            {
            }
            return primaryKeyDictionary;
        }

        /// <summary>
        /// Gets a set of ForeignKeys,the table name is used as key and a list of ForeignKey is used as value.
        /// </summary>
        /// <returns></returns>
        public virtual Dictionary<string, List<ForeignKey>> ListForeignKeys()
        {
            return GetReferenceTableNames();
        }


        /// <summary>
        /// Gets a set of ForeignKeys,the table name is used as key and a list of ForeignKey is used as value.
        /// </summary>
        /// <returns></returns>
        private Dictionary<string, List<ForeignKey>> GetReferenceTableNames()
        {
            Dictionary<string, List<ForeignKey>> keys = new Dictionary<string, List<ForeignKey>>();
            List<ForeignKey> foreignKeysOfOneTable = null;
            //DataTable foreignKeyTable = GetForeignKeyTable();

            DataTable foreignKeyTable = this.Database.CreateViewCommandExecutor().ExecuteDataTable(
                this.ListForeignKeySql(), false);

            if (foreignKeyTable == null)
            {
                return keys;
            }

            foreach (DataRow row in foreignKeyTable.Rows)
            {
                ForeignKey foreignKey = new ForeignKey();
                foreignKey.TableName = row["TableName"].ToString();
                foreignKey.Key = row["ForeignKey"].ToString();
                foreignKey.ReferenceTableName = row["ReferenceTableName"].ToString();
                foreignKey.ReferenceKey = row["ReferenceKey"].ToString();
                if (!keys.ContainsKey(foreignKey.TableName))
                {
                    foreignKeysOfOneTable = new List<ForeignKey>();
                    foreignKeysOfOneTable.Add(foreignKey);
                    keys.Add(foreignKey.TableName, foreignKeysOfOneTable);
                }
                else
                {
                    keys[foreignKey.TableName].Add(foreignKey);
                }
            }
            return keys;
        }


        public virtual List<ProcedureParameter> ListProcedureParameters()
        {
            List<ProcedureParameter> procedureParameters = new List<ProcedureParameter>();
            DataTable procedureParameterTable = this.Database.CreateViewCommandExecutor().ExecuteDataTable(
                this.ListProcedureParametersSql(), false);

            foreach (DataRow row in procedureParameterTable.Rows)
            {
                ProcedureParameter parameter = new ProcedureParameter();
                parameter.ProcedureName = row["ProcedureName"].ToString();
                parameter.ParameterName = row["ParameterName"].ToString();
                parameter.ParameterType = row["ParameterType"].ToString();
                parameter.Length = row["Length"].ToString();
                parameter.IsOutput = row["IsOutput"].ToString();
                procedureParameters.Add(parameter);
            }
            return procedureParameters;
        }


        /// <summary>
        /// Gets a list of table names
        /// </summary>
        /// <returns></returns>
        public virtual List<string> ListTableNames()
        {
            List<string> names = new List<string>();
            DataTable tableNames = this.Database.CreateViewCommandExecutor().ExecuteDataTable(
                this.ListTableNamesSql(), false);

            foreach (DataRow row in tableNames.Rows)
            {
                names.Add(row["TableName"].ToString());
            }
            return names;
        }

        /// <summary>
        ///  Gets a list of view names
        /// </summary>
        /// <returns></returns>
        public virtual List<string> ListViewNames()
        {
            List<string> names = new List<string>();
            DataTable tableNames = this.Database.CreateViewCommandExecutor().ExecuteDataTable(
                this.ListViewNamesSql(), false);

            foreach (DataRow row in tableNames.Rows)
            {
                names.Add(row["TableName"].ToString());
            }
            return names;
        }


        public DataSet LoadTableSchemas()
        {
            return LoadTableSchemas(true);
        }


        public DataSet LoadViewSchemas()
        {
            return LoadTableSchemas(false);
        }


        private DataSet LoadTableSchemas(bool isTable)
        {
            DataSet schemaDataSet = new DataSet();
            List<string> tableNames;
            tableNames = isTable ? this.ListTableNames() : this.ListViewNames();
            try
            {
                this.Database.Open();

                foreach (string tableName in tableNames)
                {
                    DataTable tempTable = this.Database.ExecuteDataTable("select * from " + tableName + " where 1 = 2", false);
                    tempTable.TableName = tableName;
                    if (tempTable != null)
                    {
                        schemaDataSet.Tables.Add(tempTable);
                    }
                }
                this.Database.Close();
            }
            catch
            {
                this.Database.Close();
            }

            return schemaDataSet;
        }
        




        public abstract void ShowAboutBox();

        /// <summary>
        /// Gets a table of identity names,which should contain a column named IdentityName.
        /// </summary>
        /// <returns></returns>
        protected abstract string ListIdentitiesSql();


        /// <summary>
        /// Gets a table of identity names,which should contain a column named PrimaryKey and a column named TableName.
        /// </summary>
        /// <returns></returns>
        protected abstract string ListPrimaryKeysSql();


        /// <summary>
        /// Gets a table of table names,which should contain a column named ForeignKey and a column named TableName.
        /// </summary>
        /// <returns></returns>
        protected abstract string ListForeignKeySql();

        /// <summary>
        /// 
        /// </summary>
        /// <returns></returns>
        protected abstract string ListProcedureParametersSql();

        /// <summary>
        /// Gets a table of table names,which should contain a column named TableName.
        /// </summary>
        /// <returns></returns>
        protected abstract string ListTableNamesSql();

        /// <summary>
        /// Gets a table of view names,which should contain a column named TableName.
        /// </summary>
        /// <returns></returns>
        protected abstract string ListViewNamesSql();
    }

}
